﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;

namespace BaseDatos
{
    public class BDVentas
    {
        private static string conexionCentral = "Data Source=.\\sqlexpress; Initial Catalog=FarmaciaAurora; Integrated Security=true";
        private static string conexionSucursal1 = "Data Source=.\\sqlexpress; Initial Catalog=FarmaciaAuroraSucursal; Integrated Security=true";
        private static string conexionSucursal2 = "Data Source=.\\sqlexpress; Initial Catalog=FarmaciaAuroraSucursal2; Integrated Security=true";
        public static int cantidadProductoSucursal1(string codigo)
        {
            int cantidad = 0;
               try
            {
                SqlConnection canal = new SqlConnection(conexionSucursal1);
                SqlCommand comando = canal.CreateCommand();
                comando.CommandText = "Select L.IdLote,Sum(L.Cantidad) as Cant From [Almacen] as A,[Lote] as L Where A.IdProducto=@prod and A.IdLote=L.IdLote and L.Caducado='False' Group By L.IdLote";
                comando.Parameters.AddWithValue("prod", codigo);
                canal.Open();
                SqlDataReader reader = comando.ExecuteReader();
                
                if (reader.Read())
                {
                     cantidad=Convert.ToInt32(reader["Cant"]);
                }
                else
                    throw new Exception("No contamos con este producto");
                return cantidad;
             }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public static int cantidadProductoSucursal2(string codigo)
        {
            int cantidad = 0;
            try
            {
                SqlConnection canal = new SqlConnection(conexionSucursal1);
                SqlCommand comando = canal.CreateCommand();
                comando.CommandText = "Select L.IdLote,Sum(L.Cantidad) as Cant From [Almacen] as A,[Lote] as L Where A.IdProducto=@prod and A.IdLote=L.IdLote and L.Caducado='False' Group By L.IdLote";
                comando.Parameters.AddWithValue("prod", codigo);
                canal.Open();
                SqlDataReader reader = comando.ExecuteReader();

                if (reader.Read())
                {
                    cantidad = Convert.ToInt32(reader["Cant"]);
                }
                else
                    throw new Exception("No contamos con este producto");
                return cantidad;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public static List<long> getLotes(string id)
        {
            try
            {
                SqlConnection canal = new SqlConnection(conexionCentral);
                SqlCommand comando = canal.CreateCommand();
                comando.CommandText = "Select A.IdLote From [Almacen] as A,[Lote] as L Where A.IdProducto=@prod and A.IdLote=L.IdLote Order By FechaVencimiento";
                comando.Parameters.AddWithValue("prod", id);
                canal.Open();
                SqlDataReader reader = comando.ExecuteReader();
                List<long> lotes = new List<long>();
                while (reader.Read())
                {
                    lotes.Add(Convert.ToInt64(reader["IdLote"]));
                }
                return lotes;
             }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public static void disminuirAlmacen(string id, int cantidad)
        {
            SqlConnection sql = null;
            
            try
            {
                List<long> lotes = getLotes(id);
                int cant = 0,c=0,canti=cantidad;
                sql = new SqlConnection(conexionCentral);
                SqlCommand comando;
                sql.Open();
                SqlDataReader reader;
                for (int i = 0; i < lotes.Count && cant < cantidad; i++)
                {
                    comando = sql.CreateCommand();
                    comando.CommandText = "Select Cantidad From [Lote] Where IdLote=@id";
                    comando.Parameters.AddWithValue("id", lotes[i]);
                    reader = comando.ExecuteReader();
                    if (reader.Read())
                        c = Convert.ToInt32(reader[0]);
                    comando = sql.CreateCommand();
                    if (canti < c)
                        c = canti;
                    cant += c;
                    canti -= c;
                    reader.Dispose();
                    comando.Dispose();
                    comando.CommandText = "Update [Lote] Set Cantidad= Cantidad-@cant where IdLote=@id";
                    comando.Parameters.AddWithValue("cant", c);
                    comando.Parameters.AddWithValue("id", lotes[i]);
                    comando.ExecuteNonQuery();
                }
            }
            catch (Exception e)
            {

                throw e;
            }
            finally
            {
                if (sql != null)
                    sql.Dispose();
            }
        }
        public static void registrarVenta(DateTime fecha, string ciCliente, string ciEmpleado, Modelo.Venta venta, string factura)
        {
            try
            {
                SqlConnection canal = new SqlConnection(conexionCentral);
                SqlCommand comando = canal.CreateCommand();
                comando.CommandText = "Insert Into [Venta](CiCliente,CiEmpleado,Fecha,IdProductoAlmacen,Cantidad,Total,IdFactura) Values (@cli,@emp,@fec,@prod,@cant,@tot,@fac)";
                comando.Parameters.AddWithValue("cli", ciCliente);
                comando.Parameters.AddWithValue("emp", ciEmpleado);
                comando.Parameters.AddWithValue("fec", fecha);
                comando.Parameters.AddWithValue("prod", venta.IdProducto);
                comando.Parameters.AddWithValue("cant", venta.Cantidad);
                comando.Parameters.AddWithValue("tot", venta.Total);
                comando.Parameters.AddWithValue("fac", factura);
                canal.Open();
                comando.ExecuteNonQuery() ;
                disminuirAlmacen(venta.IdProducto, Convert.ToInt32(venta.Cantidad));
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public static string insertarFactura(string NITSucursal, string AutorizacionNro, string Detalle)
        {
            string clave;
            try
            {
                SqlConnection canal = new SqlConnection(conexionCentral);
                SqlCommand comando = canal.CreateCommand();
                comando.CommandText = "Insert Into [Factura](NITSucursal,Detalle) Values (@nit,@det) SELECT SCOPE_IDENTITY() as Num";
                comando.Parameters.AddWithValue("nit", NITSucursal);
                comando.Parameters.AddWithValue("det", Detalle);
                canal.Open();
                SqlDataReader reader = comando.ExecuteReader();
                if (reader.Read())
                {
                    clave = reader["Num"].ToString();
                }
                else
                    throw new Exception("Excepcion no esperada consulte con el administrador");
                return clave;
            }
             catch (Exception ex)
            {
                throw ex;
            }
        }
    }
}
